﻿<%@ WebHandler Language="C#" Class="MfgorderStepStatusReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using ReportBusiness;

/**
 * Description: 生产订单工序状态查询报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.7.7
 */

public class MfgorderStepStatusReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getCompute":
                        r = getCompute(jObject);//统计数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getSearchSqlString(JObject jObject)
    {
        string sql = @"
SELECT m.*
FROM
(
SELECT cci.familylistnames,cci.productname,cci.productdescription,cci.mfgordername,mfg.qty mfgorderqty,mfg.plannedcompletiondate,cci.togetherflag,cci.qty,
       cci.containername,ws.workflowstepname,
       f2.factoryname specfactoryname,
       CASE WHEN f2.factoryid <> cci.originalfactoryid THEN '转工序' ELSE '' END spectype,
       ws.sequence,cci.workcentername,
       CASE WHEN csp.containerstepplanid IS NULL THEN '未排产' ELSE '已排产' END isplan,
       csp.plannedfinishdate,NVL(MAX(csd.checkdate),csr.recivedate) checkdate,MAX(csd.dispatchdate) dispatchdate,
       e.fullname containerstartemp,e2.fullname stepplanemp,csp.stepplancreatedate,
       CASE WHEN cci.isinstore = 1 THEN '完工入库'
            WHEN cci.isinstore <> 1 THEN DECODE(NVL(MAX(csd.checkdate),csr.recivedate),NULL,'未完成','已完成') 
       END stepstatus,
       CASE WHEN csp.containerstepplanid IS NULL THEN '主制分厂未排产'
            WHEN csp.plannedfinishdate IS NULL THEN '承制分厂未排产'
            WHEN NVL(MAX(csd.checkdate),csr.recivedate) IS NULL AND TRUNC(SYSDATE) <= TRUNC(csp.plannedfinishdate) THEN '未完成'
            WHEN NVL(MAX(csd.checkdate),csr.recivedate) IS NULL AND TRUNC(SYSDATE) > TRUNC(csp.plannedfinishdate) THEN '超期未完成'
            WHEN NVL(MAX(csd.checkdate),csr.recivedate) IS NOT NULL AND TRUNC(NVL(MAX(csd.checkdate),csr.recivedate)) <= TRUNC(csp.plannedfinishdate) THEN '按时完成'
            WHEN NVL(MAX(csd.checkdate),csr.recivedate) IS NOT NULL AND TRUNC(NVL(MAX(csd.checkdate),csr.recivedate)) > TRUNC(csp.plannedfinishdate) THEN '超期完成'  
       END finishstatus
FROM containercurrentinfo cci
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = cci.mfgorderid
LEFT JOIN containerinstore cid ON cid.charg = cci.containername
LEFT JOIN Workflowstep ws ON ws.workflowid =  NVL(cid.workflowid,cci.workflowid)
LEFT JOIN containerstepplan csp ON csp.containerid = cci.containerid AND csp.workflowstepid = ws.workflowstepid
LEFT JOIN containersteprecord csd ON csd.containerid = cci.containerid AND csd.workflowstepid = ws.workflowstepid
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN employee e ON e.employeeid = c.startemployeeid
LEFT JOIN employee e2 ON e2.employeeid = csp.stepplancreateemployeeid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
LEFT JOIN factory f2 ON f2.factoryid = s.factoryid
LEFT JOIN containershiprecive csr ON csr.containerid = cci.containerid AND csr.workflowstepid = ws.workflowstepid AND csr.isuse = 0
LEFT JOIN containerstepnoship csn ON csn.containerid = cci.containerid AND csn.workflowstepid = ws.workflowstepid AND csn.noship = 2 AND csn.isuse = 0
LEFT JOIN containercommidetail ccm ON ccm.containerid = csp.containerid AND ccm.workflowstepid = csp.workflowstepid AND ccm.isuse = 0
WHERE cci.workflowroute IS NULL AND cci.containerstatus = 1 AND cci.isinstore <> -1 
      AND NVL(s.controlcode,'LY01') <> 'LY05' AND ccm.containercommidetailid IS NULL AND csn.containerstepnoshipid IS NULL"; 
        // 主制分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }

        // 主制工区
        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND cci.fromworkcenterid = '{0}'", jObject["workcenterid"].ToString());
        }

        // 机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND cci.familylistnames = '{0}'", jObject["productfamilyname"].ToString());
        }

        // 件号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        // 订单
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        // 排产人
        if (jObject["stepplancreateemployeeid"] != null && jObject["stepplancreateemployeeid"].ToString() != "")
        {
            sql += string.Format(" AND csp.stepplancreateemployeeid = '{0}'", jObject["stepplancreateemployeeid"].ToString());
        }

        // 开卡人
        if (jObject["startemployeeid"] != null && jObject["startemployeeid"].ToString() != "")
        {
            sql += string.Format(" AND c.startemployeeid = '{0}'", jObject["startemployeeid"].ToString());
        }

        //是否排产
        if (jObject["isplan"] != null && jObject["isplan"].ToString() != "")
        {
            if (jObject["isplan"].ToString() == "已排产")
            {
                sql += " AND NVL(csp.containerstepplanid,'0') <> '0'";
            }
            else
            {
                sql += " AND NVL(csp.containerstepplanid,'0') = '0'";
            }
        }

        //排产完成时间
        if (jObject["plannedfinishdate"] != null && jObject["plannedfinishdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["plannedfinishdate"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND TRUNC(csp.plannedfinishdate) >= TO_DATE('{0}','yyyy-mm-dd')", arr[0].ToString());
                sql += string.Format(" AND TRUNC(csp.plannedfinishdate) <= TO_DATE('{0}','yyyy-mm-dd')", arr[1].ToString());
            }
        }

        //订单计划完成时间
        if (jObject["plannedcompletiondate"] != null && jObject["plannedcompletiondate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["plannedcompletiondate"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND mfg.plannedcompletiondate >= TO_DATE('{0}','yyyy-mm-dd')", arr[0].ToString());
                sql += string.Format(" AND mfg.plannedcompletiondate <= TO_DATE('{0}','yyyy-mm-dd')", arr[1].ToString());
            }
        }

        //实际完工日期
        if (jObject["checkdate"] != null && jObject["checkdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["checkdate"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND TRUNC(NVL(csd.checkdate,csr.recivedate)) >= TO_DATE('{0}','yyyy-mm-dd')", arr[0].ToString());
                sql += string.Format(" AND TRUNC(NVL(csd.checkdate,csr.recivedate)) <= TO_DATE('{0}','yyyy-mm-dd')", arr[1].ToString());
            }
        }

        //实际开始日期(派工时间)
        if (jObject["dispatchdate"] != null && jObject["dispatchdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["dispatchdate"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND csd.dispatchdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", arr[0].ToString());
                sql += string.Format(" AND csd.dispatchdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss')", arr[1].ToString());
            }
        }

        //排产操作日期
        if (jObject["stepplancreatedate"] != null && jObject["stepplancreatedate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["stepplancreatedate"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND csp.stepplancreatedate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", arr[0].ToString());
                sql += string.Format(" AND csp.stepplancreatedate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss')", arr[1].ToString());
            }
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        sql += @"
GROUP BY cci.familylistnames,cci.productname,cci.productdescription,cci.mfgordername,mfg.qty,mfg.plannedcompletiondate,cci.togetherflag,cci.qty,
         cci.containername,ws.workflowstepname,ws.sequence,cci.workcentername,csp.containerstepplanid,
         csp.plannedfinishdate,e.fullname,e2.fullname,csp.stepplancreatedate,cci.isinstore,csr.recivedate,f2.factoryname,cci.originalfactoryid,f2.factoryid
) m
WHERE 1=1 ";
        //工序状态
        if (jObject["stepstatus"] != null && jObject["stepstatus"].ToString() != "")
        {
            sql += string.Format(" AND m.stepstatus = '{0}'", jObject["stepstatus"].ToString());
        }
        
        //排产状态
        if (jObject["stepplanstatus"] != null && jObject["stepplanstatus"].ToString() != "")
        {
            sql += string.Format(" AND m.finishstatus = '{0}'", jObject["stepplanstatus"].ToString());
        }

        sql += " ORDER BY m.mfgordername,m.togetherflag,m.containername,m.sequence";
        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getCompute(JObject jObject)
    {
        Result r = new Result();
        jObject["isplan"] = "已排产";
        string sql = getSearchSqlString(jObject);
        DataTable dtDetail = dal.GetDataTable(sql);
        DataTable dtCompute = new DataTable();
        dtCompute.Columns.Add("productCount");//排产零件数
        dtCompute.Columns.Add("specCount"); //排产工序数
        dtCompute.Columns.Add("finishSpecCount");//完工工序数
        dtCompute.Columns.Add("delayfinishedSum");//超期完工数
        dtCompute.Columns.Add("delaySpecCount");// 超期工序数

        dtCompute.Columns.Add("notFinishCount");//未完工序数
        dtCompute.Columns.Add("finishRate");//完成率
        dtCompute.Columns.Add("delayFinishRate");//超期完成率
        dtCompute.Columns.Add("intimeFinishRate");// 按时完成率

        dtCompute.Columns.Add("intimeFinishCount");//按时完工数
        dtCompute.Columns.Add("notFinishRate");//未完成率

        DataRow dr = dtCompute.NewRow();

        DataTable dtProductCount = dtDetail.DefaultView.ToTable(true, "productname");
        //计算排产零件数
        if (dtProductCount.Rows.Count > 0)
        {
            dr["productCount"] = dtProductCount.Rows.Count;
        }
        else
        {
            dr["productCount"] = 0;
        }

        //计算排产工序数
        dr["specCount"] = dtDetail.Select("isplan = '已排产'").Length;

        //计算完工工序数
        DataRow[] drFinish = dtDetail.Select("finishstatus = '超期完成' OR finishstatus = '按时完成'");
        if (drFinish.Length > 0)
        {
            dr["finishSpecCount"] = drFinish.Length;
        }
        else
        {
            dr["finishSpecCount"] = 0;
        }

        //计算超期完工数
        DataRow[] drDelayFinish = dtDetail.Select("finishstatus = '超期完成' ");
        if (drDelayFinish.Length > 0)
        {
            dr["delayfinishedSum"] = drDelayFinish.Length;
        }
        else
        {
            dr["delayfinishedSum"] = 0;
        }


        //计算超期工序数
        DataRow[] drDelaySpecCount = dtDetail.Select("finishstatus = '超期完成' OR finishstatus = '超期未完成'");
        if (drDelaySpecCount.Length > 0)
        {
            dr["delaySpecCount"] = drDelaySpecCount.Length;
        }
        else
        {
            dr["delaySpecCount"] = 0;
        }

        // 计算未完工工序数 = 排产工序数 - 完工工序数
        dr["notFinishCount"] = Convert.ToInt32(dr["specCount"].ToString()) - Convert.ToInt32(dr["finishSpecCount"].ToString());

        // 完工工序数
        int finishSpecCount = Convert.ToInt32(dr["finishSpecCount"].ToString());

        // 工序总数
        int specCount = Convert.ToInt32(dr["specCount"].ToString());

        // 计算完成率
        if (specCount > 0)
        {
            decimal finishRate = ((decimal)finishSpecCount / specCount) * 100;// 完成率 = 完工工序数 / 工序总数
            dr["finishRate"] = finishRate.ToString("0.00");
        }
        else
        {
            dr["finishRate"] = "--";
        }

        //超期完工数
        int delayFinishCount = Convert.ToInt32(dr["delayfinishedSum"].ToString());

        if (specCount > 0)
        {
            //超期完成率
            decimal delayFinishRate = ((decimal)delayFinishCount / specCount) * 100;// 超期完成率 = 超期完成率 / 工序总数
            dr["delayFinishRate"] = delayFinishRate.ToString("0.00");

            //按时完成率
            decimal intimeFinishRate = ((decimal)(finishSpecCount - delayFinishCount) / specCount) * 100;
            dr["intimeFinishRate"] = intimeFinishRate.ToString("0.00");
        }
        else
        {
            dr["delayFinishRate"] = "--";
            dr["intimeFinishRate"] = "--";
        }

        //按时完工数
        dr["intimeFinishCount"] = dtDetail.Select("finishstatus = '按时完成'").Length;

        //未完成率 = 100% - 完成率
        if (dr["finishRate"].ToString() != "--")
        {
            dr["notFinishRate"] = 100 - Convert.ToDecimal(dr["finishRate"]);
        }
        else
        {
            dr["notFinishRate"] = "--";
        }

        dtCompute.Rows.Add(dr);
        r.data = dtCompute;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dt.Columns.Remove("sequence");
        dt.Columns["familylistnames"].Caption = "机型";
        dt.Columns["productname"].Caption = "零件图号";
        dt.Columns["productdescription"].Caption = "零件名称";
        dt.Columns["mfgordername"].Caption = "订单号";
        dt.Columns["mfgorderqty"].Caption = "订单数";
        dt.Columns["togetherflag"].Caption = "批次号";
        dt.Columns["qty"].Caption = "批次数";
        dt.Columns["plannedcompletiondate"].Caption = "订单计划完成时间";
        dt.Columns["containername"].Caption = "流水卡号";
        dt.Columns["workflowstepname"].Caption = "工序";
        dt.Columns["specfactoryname"].Caption = "工序所属分厂";
        dt.Columns["spectype"].Caption = "工序类型";
        dt.Columns["workcentername"].Caption = "责任工区";
        dt.Columns["isplan"].Caption = "是否已排产";
        dt.Columns["plannedfinishdate"].Caption = "排产计划完成时间";
        dt.Columns["checkdate"].Caption = "实际完成时间";
        dt.Columns["dispatchdate"].Caption = "实际开始时间";
        dt.Columns["containerstartemp"].Caption = "开卡人";
        dt.Columns["stepplanemp"].Caption = "排产人";
        dt.Columns["stepplancreatedate"].Caption = "排产操作时间";
        dt.Columns["stepstatus"].Caption = "工序状态";
        dt.Columns["finishstatus"].Caption = "排产状态";
        ExportHelper.ExportExcel(dt, "生产订单工序状态查询报表");
    }
}